'''
Created on 2020年1月30日
获取所有股票数据
@author: JM
'''
import time
from datetime import datetime
from sqlalchemy import text

import pandas as pd

from app_config import get_pro, get_engine_ts

pro = get_pro()
engine_ts = get_engine_ts()


# def read_data():
#     sql = """SELECT * FROM daily"""
#     df = pd.read_sql_query(sql, engine_ts)
#     return df


def write_data(df_):
    res = df_.to_sql('fut_daily', engine_ts, index=False, if_exists='append', chunksize=5000)
    print(res)


def get_data(ts_code):
    df = pro.fut_daily(ts_code=ts_code)
    return df


def get_daily(ts_code):
    for _ in range(10):
        try:
            df = get_data(ts_code)
            # # 创建新的列 'id'，将 'ts_code' 和 'trade_date' 两列拼接起来
            # df['id'] = df['ts_code'].astype(str) + df['trade_date'].astype(str)
            write_data(df)
        except Exception as e:
            print(f"An unexpected error occurred: {e}")
            time.sleep(10)
        else:
            return
    print("error error error error error" + ts_code)


def remove_product(ts_code_):
    # 使用 text() 构造带参数的 SQL
    sql = text("DELETE FROM fut_daily WHERE ts_code = :param1")
    # 执行 SQL
    with engine_ts.connect() as connection:
        result = connection.execute(sql, {"param1": ts_code_})
        connection.commit()
        print(f"删除了 {result.rowcount} 行")
    pass


def check_has_data(ts_code) -> bool:
    query = text("SELECT * FROM fut_daily WHERE ts_code = :param1 LIMIT 1")
    with engine_ts.connect() as conn:
        df = pd.read_sql(query, conn, params={'param1': ts_code})
        if not df.empty:
            return True
        else:
            return False


def fetch_data(fut_start, fut_end, fut_code):
    mons = [f"{y:02d}{m:02d}"
            for y in range(00, 26)
            for m in range(1, 13)
            if fut_start <= f"{y:02d}{m:02d}" <= fut_end]

    now_year_mon = datetime.now().strftime('%y%m')

    for mon in mons:
        ts_code = fut_code + mon + '.CFX'
        print(f"""{ts_code} start {now_year_mon}""")
        if mon < now_year_mon:
            has_data = check_has_data(ts_code)
            print(f"""history futures: {has_data}""")
            if not has_data:
                get_daily(ts_code)
        else:
            remove_product(ts_code)
            get_daily(ts_code)


if __name__ == '__main__':
    # fetch_data('2001', '2510', 'IC')
    fetch_data('1501', '2510', 'IC')
